package com.mbc.rmt.dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

import com.mbc.common.dao.DAO;
import com.mbc.common.entities.Branch;
import com.mbc.common.param.CriteriaParams;
import com.mbc.rmt.data.CashRemittanceByAgentNDetailDataList;
import com.mbc.rmt.data.DefinePaymentTypeDataList;
import com.mbc.rmt.data.DefinePaymentTypeDetailDataList;
import com.mbc.rmt.entities.Agent;
import com.mbc.rmt.entities.CashRemittanceByAgentDetails;
import com.mbc.rmt.entities.DefinePaymentType;
import com.mbc.rmt.entities.DefinePaymentTypeDetail;

public class DefinePaymentTypeDetailDAOImpls extends DAO<DefinePaymentTypeDetail> implements DefinePaymentTypeDetailDAO {

	public static final String field_detail_id = "DetailID";
	public static final String field_receipt_id = "ReceiptID";
	public static final String field_receipt_no = "ReceiptNo";
	public static final String field_invoice_id = "InvoiceID";
	public static final String field_invoice_no = "InvoiceNo";
	public static final String field_invoice_date = "InvoiceDate";
	public static final String field_addcode_id = "AddCodeID";
	public static final String field_addcode_code = "Code";
	public static final String field_sender = "Sender";
	public static final String field_receiver = "Receiver";
	public static final String field_address = "Address";
	public static final String field_currency_id = "CurrencyID";
	public static final String field_currency_code = "CurrencyCode";
	public static final String field_type_id = "TypeID";
	public static final String field_type_code = "TypeCode";
	public static final String field_amount = "Amount";
	public static final String field_agent_id = "AgentID";
	public static final String field_agent_code = "AgentCode";
	public static final String field_slip_no = "Slip_No";
	public static final String field_created_date = "CreatedDate";
	public static final String field_created_by = "CreatedBy";
	public static final String field_modified_date = "ModifiedDate";
	public static final String field_modified_by = "ModifiedBy";
	public static final String field_paidstatus = "PaidStatus";
	public static final String field_transtatus = "TranStatus";
	
	//CRBA Archive
	public static final String field_receipt_detail_id = "ReceiptDetailID";
	public static final String field_paymentcurrency_id = "PaymentCurrencyID";
	public static final String field_paymentcurrency_code = "CurrencyCode";
	public static final String field_paymentamount = "PaymentAmount";
	public static final String field_receivedamount = "ReceivedAmount";
	public static final String field_extrafees = "ExtraFees";
	public static final String field_nrc = "NRC";
	public static final String field_toagent_id = "ToAgentID";
	public static final String field_tobranch_id = "ToBranchID";
	public static final String field_status = "Status";
	
	public DefinePaymentTypeDetailDAOImpls(Connection conn) {
		this.conn = conn;
		this.table_name = "DefinePaymentTypeDetail";
	}
	
	enum TRAN_MODE { SAVE, UPDATE }
	
	@Override
	public void create(DefinePaymentTypeDetail entity) throws SQLException {
		
		query = insertParam(table_name, new String[] { field_detail_id, field_receipt_id, field_receipt_detail_id, field_receipt_no, 
					field_invoice_id, field_invoice_no, field_addcode_id, field_addcode_code, field_sender,
					field_receiver, field_nrc, field_address, field_currency_id, field_type_id, field_type_code, field_extrafees, field_amount, field_created_date, field_created_by, field_transtatus });
		
		pstmt = conn.prepareStatement(query);
		updateRecord(entity, TRAN_MODE.SAVE);
		pstmt.executeUpdate();
		pstmt.close();
	}

	@Override
	public void update(DefinePaymentTypeDetail entity) throws SQLException {
		query = updateParam(table_name, new String[] { field_detail_id, field_receipt_id, field_receipt_detail_id, field_receipt_no, 
				field_invoice_id, field_invoice_no, field_addcode_id, field_addcode_code, field_sender, 
				field_receiver, field_nrc, field_address, field_currency_id, field_type_id, field_type_code, field_extrafees, field_amount, field_modified_date, field_modified_by}) + " AND " + field_detail_id + " = " + entity.getDetailId();
		
		pstmt = conn.prepareStatement(query);
		updateRecord(entity, TRAN_MODE.UPDATE);
		pstmt.executeUpdate();
		pstmt.close();
	}
	
	@Override
	public void rollbackAgent(long detailid) throws SQLException {
		query = updateParam(table_name, new String[] { field_agent_id, field_agent_code, field_slip_no }) + " AND " + field_detail_id + " = " + detailid;
		
		pstmt = conn.prepareStatement(query);
		
		int i = 1;
		pstmt.setString(i++, null);
		pstmt.setString(i++, null);
		pstmt.setString(i++, null);
		
		pstmt.executeUpdate();
		pstmt.close();
	}

	@Override
	public void delete(Long key) throws SQLException {
		query = getDeleteQuery() + " WHERE " + field_detail_id + " = " + key;
		pstmt = conn.prepareStatement(query);
		pstmt.executeUpdate();
		pstmt.close();
		
	}
	
	@Override
	public void deleteByInvoiceId(Long key) throws SQLException {
		query = getDeleteQuery() + " WHERE " + field_invoice_id + " = " + key;
		pstmt = conn.prepareStatement(query);
		pstmt.executeUpdate();
		pstmt.close();
	}

	@Override
	public DefinePaymentTypeDetail find(String receiptNo) throws SQLException {
		DefinePaymentTypeDetail l_entity = null;
		query = getSelectQuery() + " WHERE " + field_receipt_no + "'" + receiptNo + "'";
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		if(rs.next()){
			l_entity = readRecord(rs);
		}
		return l_entity;
	}

	@Override
	public DefinePaymentTypeDetail findById(Long key) throws SQLException {
		DefinePaymentTypeDetail l_entity = null;
		query = "SELECT d.*, c.CurrencyCode FROM " + table_name + " d INNER JOIN Currency c ON c.CurrencyId = d.CurrencyID WHERE " + field_detail_id + " = " +key;
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		if(rs.next()){
			l_entity = readRecord(rs);
		}
		return l_entity;
	}
	
	@Override
	public ArrayList<DefinePaymentTypeDetail> findByInvoiceId(long key) throws SQLException {
		ArrayList<DefinePaymentTypeDetail> l_list = new ArrayList<DefinePaymentTypeDetail>();
		query = "SELECT d.*, c.CurrencyCode FROM " + table_name + " d INNER JOIN Currency c ON c.CurrencyId = d.CurrencyID WHERE " + field_invoice_id + " = " + key;
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		while(rs.next()){
			l_list.add(readRecord(rs));
		}
		rs.close();
		pstmt.close();
		
		if(l_list.size() == 0)
			return null;
		
		return l_list;
	}

	@Override
	public DefinePaymentTypeDetailDataList findDPTypeList(CriteriaParams param)
			throws SQLException {
		DefinePaymentTypeDetailDataList l_DataList = new DefinePaymentTypeDetailDataList();
		ArrayList<DefinePaymentTypeDetail> l_entityList = new ArrayList<DefinePaymentTypeDetail>();
		
		query = "SELECT * FROM (SELECT ROW_NUMBER()OVER (ORDER BY ReceiptID)No,dptd.* " +
				" FROM DefinePaymentTypeDetail dptd " +
				" WHERE 1 = 1 )a " +
				" WHERE No > " + param.getIndex();
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		while(rs.next()){
			l_entityList.add(readRecord(rs));
		}
		l_DataList.setEntityList(l_entityList);
		rs.close();
		pstmt.close();
		query = "select count(ReceiptID) from " + table_name + " cra where 1 = 1";
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		if (rs.next()) {
			l_DataList.setTotalRecord(rs.getLong(1));
		}
		rs.close();
		pstmt.close();
		return l_DataList;
	}

	@Override
	protected DefinePaymentTypeDetail readRecord(ResultSet rs)
			throws SQLException {
		DefinePaymentTypeDetail l_detailEntity = new DefinePaymentTypeDetail();
		l_detailEntity.setDetailId(rs.getLong(field_detail_id));
		l_detailEntity.setReceiptId(rs.getLong(field_receipt_id));
		l_detailEntity.setReceiptDetailId(rs.getLong(field_receipt_detail_id));
		l_detailEntity.setReceiptNo(rs.getString(field_receipt_no));
		l_detailEntity.setInvoiceId(rs.getLong(field_invoice_id));
		l_detailEntity.setInvoiceCode(rs.getString(field_invoice_no));
		l_detailEntity.setAddCodeId(rs.getLong(field_addcode_id));
		l_detailEntity.setAddCodeCode(rs.getString(field_addcode_code));
		l_detailEntity.setSender(rs.getString(field_sender));
		l_detailEntity.setCurrencyId(rs.getLong(field_currency_id));
		l_detailEntity.setCurrencyCode(rs.getString(field_currency_code));
		l_detailEntity.setTypeId(rs.getLong(field_type_id));
		l_detailEntity.setTypeCode(rs.getString(field_type_code));
		l_detailEntity.setAmount(rs.getDouble(field_amount));
		l_detailEntity.setExtrafees(rs.getDouble(field_extrafees));
		l_detailEntity.setNrc(rs.getString(field_nrc));
		l_detailEntity.setReceiver(rs.getString(field_receiver));
		l_detailEntity.setAddress(rs.getString(field_address));
		l_detailEntity.setAgentId(rs.getLong(field_agent_id));
		l_detailEntity.setAgentCode(rs.getString(field_agent_code));
		l_detailEntity.setSlip_No(rs.getString(field_slip_no));
		l_detailEntity.setCreatedDate(rs.getTime(field_created_date));
		l_detailEntity.setCreatedBy(rs.getLong(field_created_by));
		l_detailEntity.setModifiedDate(rs.getTime(field_modified_date));
		l_detailEntity.setModifiedBy(rs.getLong(field_modified_by));
		l_detailEntity.setTranStatus(rs.getByte(field_transtatus));
		try {
			l_detailEntity.setPaidStatus(rs.getString(field_paidstatus));
		} catch(Exception e) { }
		return l_detailEntity;
	}
	
	private DefinePaymentTypeDetail readRecordAB(ResultSet rs)
		throws SQLException {
		DefinePaymentTypeDetail l_detailEntity = new DefinePaymentTypeDetail();
		l_detailEntity.setDetailId(rs.getLong(field_detail_id));
		l_detailEntity.setReceiptId(rs.getLong(field_receipt_id));
		l_detailEntity.setReceiptDetailId(rs.getLong(field_receipt_detail_id));
		l_detailEntity.setReceiptNo(rs.getString(field_receipt_no));
		l_detailEntity.setInvoiceId(rs.getLong(field_invoice_id));
		l_detailEntity.setInvoiceCode(rs.getString(field_invoice_no));
		l_detailEntity.setAddCodeId(rs.getLong(field_addcode_id));
		l_detailEntity.setAddCodeCode(rs.getString(field_addcode_code));
		l_detailEntity.setSender(rs.getString(field_sender));
		l_detailEntity.setCurrencyId(rs.getLong(field_currency_id));
		l_detailEntity.setCurrencyCode(rs.getString(field_currency_code));
		l_detailEntity.setTypeId(rs.getLong(field_type_id));
		l_detailEntity.setAmount(rs.getDouble(field_amount));
		l_detailEntity.setExtrafees(rs.getDouble(field_extrafees));
		l_detailEntity.setNrc(rs.getString(field_nrc));
		l_detailEntity.setReceiver(rs.getString(field_receiver));
		l_detailEntity.setAddress(rs.getString(field_address));
		l_detailEntity.setAgentId(rs.getLong(field_agent_id));
		l_detailEntity.setAgentCode(rs.getString(field_agent_code));
		l_detailEntity.setSlip_No(rs.getString(field_slip_no));
		l_detailEntity.setCreatedDate(rs.getTime(field_created_date));
		l_detailEntity.setCreatedBy(rs.getLong(field_created_by));
		l_detailEntity.setModifiedDate(rs.getTime(field_modified_date));
		l_detailEntity.setModifiedBy(rs.getLong(field_modified_by));
		
		l_detailEntity.setInvoiceCode(rs.getString(field_invoice_no));
		l_detailEntity.setInvoiceDate(rs.getTime(field_invoice_date));
		
		l_detailEntity.setPaidStatus(rs.getString(field_paidstatus));
		
		return l_detailEntity;
	}
	
	protected void updateRecord(DefinePaymentTypeDetail entity, TRAN_MODE mode) throws SQLException {
		int i = 1;
		pstmt.setLong(i++, entity.getDetailId());
		pstmt.setLong(i++, entity.getReceiptId());
		pstmt.setLong(i++, entity.getReceiptDetailId());
		pstmt.setString(i++, entity.getReceiptNo());
		pstmt.setLong(i++, entity.getInvoiceId());
		pstmt.setString(i++, entity.getInvoiceCode());
		pstmt.setLong(i++, entity.getAddCodeId());
		pstmt.setString(i++, entity.getAddCodeCode());
		pstmt.setString(i++, entity.getSender());
		pstmt.setString(i++, entity.getReceiver());
		pstmt.setString(i++, entity.getNrc());
		pstmt.setString(i++, entity.getAddress());
		pstmt.setLong(i++, entity.getCurrencyId());
		pstmt.setLong(i++, entity.getTypeId());
		pstmt.setString(i++, entity.getTypeCode());
		pstmt.setDouble(i++, entity.getExtrafees());
		pstmt.setDouble(i++, entity.getAmount());
		//pstmt.setLong(i++, entity.getAgentId());
		//pstmt.setString(i++, entity.getAgentCode());
		
		if(mode == TRAN_MODE.SAVE) {
			pstmt.setTimestamp(i++, new Timestamp(entity.getCreatedDate().getTime()));
			pstmt.setLong(i++, entity.getCreatedBy());
			pstmt.setByte(i++, (byte)0);
		} else {
			pstmt.setTimestamp(i++, new Timestamp(entity.getModifiedDate().getTime()));
			pstmt.setLong(i++, entity.getModifiedBy());
		}
	}
	
	public CashRemittanceByAgentNDetailDataList getConfirmData(Branch l_entity, long defaultBrand) throws SQLException {
		CashRemittanceByAgentNDetailDataList l_DataList = new CashRemittanceByAgentNDetailDataList();
		List<CashRemittanceByAgentDetails> l_entityList = new ArrayList<CashRemittanceByAgentDetails>();
		
		query = "SELECT * FROM (SELECT crbada.*, ISNULL(CONVERT(varchar(100),crbada.[status]), '') [Rstatus], a.Code, crbaa.ReceiptNo, crbaa.PaymentCurrencyID, c.CurrencyCode, crbaa.TotalPaymentAmount FROM CashRemittanceByAgentDetails_Archive crbada " + 
				"INNER JOIN CashRemittanceByAgent_Archive crbaa ON crbaa.ReceiptID = crbada.ReceiptID " +
				"INNER JOIN Currency c ON c.CurrencyID = crbaa.PaymentCurrencyID " +
				"INNER JOIN AddCode a ON a.AddCodeID = crbada.AddCodeID " +
				"INNER JOIN Branch b ON b.BranchID = crbada.ToBranchID " +
				"WHERE crbada.ToBranchID = " + defaultBrand + " AND crbaa.DefaultBranchID = " + l_entity.getBranchId() + " AND crbaa.[Status] = 2)s where s.[Rstatus] = ''";
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		while(rs.next()){
			l_entityList.add(readRecordForArchive(rs));
		}
		l_DataList.setEntityList(l_entityList);
		rs.close();
		pstmt.close();
		
		return l_DataList;
	}
	
	public CashRemittanceByAgentDetails readRecordForArchive(ResultSet rs) throws SQLException {
		CashRemittanceByAgentDetails l_info = new CashRemittanceByAgentDetails();
		l_info.setReceiptDetailId(rs.getLong(field_receipt_detail_id));
		l_info.setReceiptId(rs.getLong(field_receipt_id));
		l_info.setReceiptNo(rs.getString(field_receipt_no));
		l_info.setAddcodeId(rs.getLong(field_addcode_id));
		l_info.setCode(rs.getString(field_addcode_code));
		l_info.setSender(rs.getString(field_sender));
		l_info.setReceiver(rs.getString(field_receiver));
		l_info.setPcurrencyId(rs.getLong(field_paymentcurrency_id));
		l_info.setP_currencyCode(rs.getString(field_paymentcurrency_code));
		l_info.setPaymentAmt(rs.getDouble(field_paymentamount));
		l_info.setReceivedAmt(rs.getDouble(field_receivedamount));
		l_info.setExtraFees(rs.getDouble(field_extrafees));
		l_info.setNrc(rs.getString(field_nrc));
		l_info.setAddress(rs.getString(field_address));
		l_info.setToAgentId(rs.getLong(field_toagent_id));
		l_info.setToBranchId(rs.getLong(field_tobranch_id));
		
		return l_info;
	}
	
	@Override
	public 	DefinePaymentTypeDetailDataList getDPDbyANBList(String typecode, DefinePaymentType entity) throws SQLException {
		SimpleDateFormat l_format = new SimpleDateFormat("dd/MM/yyyy");
		DefinePaymentTypeDetailDataList l_dataList = new DefinePaymentTypeDetailDataList();
		List<DefinePaymentTypeDetail> l_entityList = new ArrayList<DefinePaymentTypeDetail>();
		
		query = "SELECT * FROM (SELECT dptd.*, ISNULL(Slip_No, '') SlipNo, c.CurrencyCode, dpt.InvoiceDate ,dpt.BranchID,(CASE dptd.ExtraFees WHEN 0 THEN 'P' ELSE '' END) AS PaidStatus " +
				"FROM DefinePaymentTypeDetail dptd " +
				"INNER JOIN DefinePaymentType dpt ON dpt.InvoiceID = dptd.InvoiceID " +
				"LEFT JOIN CashRemittanceByAgent_Archive crbaa ON crbaa.[ReceiptID] = dptd.[ReceiptID] " +
				"INNER JOIN Branch b ON b.[BranchID] = dpt.[ReceivedBranchID] " +
				"INNER JOIN Currency c ON c.[CurrencyID] = dptd.[CurrencyID] " +
				"INNER JOIN [Type] t ON t.[Type_ID] = dptd.TypeID " +
				"WHERE t.[Type_code] = '" + typecode + "' AND b.BranchCode = '" + entity.getFromBranchCode() + "' ";
		
		if(!entity.getFromReceiptNo().equals("") && !entity.getToReceiptNo().equals(""))
			query += "AND dptd.ReceiptNo BETWEEN '" + entity.getFromReceiptNo() + "' AND '" + entity.getToReceiptNo() + "'";
				
		query += ")a WHERE a.[SlipNo] = '' and convert(varchar, a.[InvoiceDate], 103) = '" + l_format.format(entity.getDPDate()) + "' and a.BranchID = " + entity.getBranchId();
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		while(rs.next()){
			l_entityList.add(readRecordAB(rs));
		}
		l_dataList.setEntityList(l_entityList);
		rs.close();
		pstmt.close();
		
		return l_dataList;
	}
	
	@Override
	public DefinePaymentTypeDetailDataList getInvoiceNo(DefinePaymentTypeDetail param) throws SQLException {
		DefinePaymentTypeDetailDataList l_dataList = new DefinePaymentTypeDetailDataList();
		List<DefinePaymentTypeDetail> l_entityList = new ArrayList<DefinePaymentTypeDetail>();
		
		String l_query = "";
		if(!param.isTyping()) {
			query = "SET ROWCOUNT " + param.getSize() + " SELECT ### FROM " +
					"(SELECT ROW_NUMBER()OVER (ORDER BY ReceiptNo)No,* FROM (SELECT DISTINCT dptd.ReceiptNo,dpt.BranchID, dptd.TypeCode, dpt.InvoiceNo ,dptd.Slip_No " +
					"FROM DefinePaymentTypeDetail dptd " +
					"inner join DefinePaymentType dpt on dpt.InvoiceID = dptd.InvoiceID)a WHERE ReceiptNo LIKE '%" + param.getKeyword() + "%' ";
			
			if(!param.getInvoiceCode().equals(""))
				query += "AND InvoiceNo = '" + param.getInvoiceCode() + "' ";
			
			query += "AND BranchID = " + param.getBranchId(); 
			
			if(!param.getTypeCode().equals(""))
				query += " and a.TypeCode = '" + param.getTypeCode() + "' and a.Slip_No is null";
			
			query += ")b";
			
			l_query = query.replace("###", "*") + " WHERE [No] > " + param.getIndex();
		} else {
			query = "SELECT DISTINCT ReceiptNo, BranchID FROM DefinePaymentTypeDetail dptd " +
					"inner join DefinePaymentType dpt on dpt.InvoiceID = dptd.InvoiceID WHERE ReceiptNo = '" + param.getReceiptNo() + "' AND BranchID = " + param.getBranchId();
			
			if(!param.getTypeCode().equals(""))
				query += " and dptd.TypeCode = '" + param.getTypeCode() + "' and dptd.Slip_No is null";
			
			l_query = query;
		}
		
		pstmt = conn.prepareStatement(l_query);
		rs = pstmt.executeQuery();
		
		while(rs.next()){
			DefinePaymentTypeDetail l_info = new DefinePaymentTypeDetail();
			l_info.setReceiptNo(rs.getString(field_receipt_no));
			l_entityList.add(l_info);
		}
		
		l_dataList.setEntityList(l_entityList);
		
		rs.close();
		pstmt.close();
		
		if(!param.isTyping()) {
			query = query.replace("###", "count(*)");
			pstmt = conn.prepareStatement(query);
			rs = pstmt.executeQuery();
			
			if(rs.next()){
				l_dataList.setTotalRecord(rs.getLong(1));
			}
			
			rs.close();
			pstmt.close();
		}
		
		return l_dataList;
	}
	
	@Override
	public void updateAgent(DefinePaymentTypeDetail entity) throws SQLException {
		query = updateParam(table_name, new String[] { field_slip_no, field_agent_id, 
				field_agent_code, field_modified_date, field_modified_by }) + " AND " + field_detail_id + " = " + entity.getDetailId();
		pstmt = conn.prepareStatement(query);
		int i = 1;
		pstmt.setString(i++, entity.getSlip_No());
		pstmt.setLong(i++, entity.getAgentId());
		pstmt.setString(i++, entity.getAgentCode());
		pstmt.setTimestamp(i++, new Timestamp(entity.getModifiedDate().getTime()));
		pstmt.setLong(i++, entity.getModifiedBy());
		pstmt.executeUpdate();
		pstmt.close();
	}

	@Override
	public DefinePaymentTypeDetailDataList getDPDbyANBList(DefinePaymentTypeDetail entity) throws SQLException {
		DefinePaymentTypeDetailDataList l_dataList = new DefinePaymentTypeDetailDataList();
		List<DefinePaymentTypeDetail> l_entityList = new ArrayList<DefinePaymentTypeDetail>();
		ArrayList<DefinePaymentTypeDetail> l_entityDetailList = new ArrayList<DefinePaymentTypeDetail>();
		
		if(entity.isTyping())
			query = getSelectQuery() + " dptd INNER JOIN Currency c ON c.CurrencyID = dptd.CurrencyID WHERE Slip_No = '" + entity.getSlip_No() + "'";
		else
			query = "SET ROWCOUNT " + entity.getSize() + " SELECT * FROM ( " +
					"SELECT ROW_NUMBER()OVER (ORDER BY Slip_No)[No], dptd.*, c.CurrencyCode, dpt.BranchID, (CASE dptd.ExtraFees WHEN 0 THEN 'P' ELSE '' END) AS PaidStatus FROM " + table_name + " dptd" +
					" INNER JOIN Currency c ON c.CurrencyID = dptd.CurrencyID " +
					" inner join DefinePaymentType dpt on dpt.InvoiceID = dptd.InvoiceID " +
					" LEFT JOIN CashRemittanceByAgent_Archive crbaa ON crbaa.[ReceiptID] = dptd.[ReceiptID] " +
					" WHERE Slip_No LIKE '%" + entity.getKeyword() + "%')a WHERE a.BranchID = " + entity.getBranchId() + " AND [No] > " + entity.getIndex();
		
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		
		while(rs.next()){
			l_entityDetailList.add(readRecord(rs));
		}
		
		rs.close();
		pstmt.close();
		
		String oldSlip = "";
		for(int i = 0; i < l_entityDetailList.size(); i++) {
			if(!oldSlip.equals(l_entityDetailList.get(i).getSlip_No()))
				l_entityList.add(l_entityDetailList.get(i));
			oldSlip = l_entityDetailList.get(i).getSlip_No();
		}
		
		for(int j = 0; j < l_entityList.size(); j++) {
			ArrayList<DefinePaymentTypeDetail> l_detail = new ArrayList<DefinePaymentTypeDetail>();
			for(int z = 0; z < l_entityDetailList.size(); z++) {
				if(l_entityList.get(j).getSlip_No().equals(l_entityDetailList.get(z).getSlip_No())) {
					l_detail.add(l_entityDetailList.get(z));
				}
			}
			l_entityList.get(j).setList(l_detail);
		}
		l_dataList.setEntityList(l_entityList);
		
		return l_dataList;
	}

	@Override
	public DefinePaymentTypeDataList findDetail(DefinePaymentType param)
			throws SQLException {
		DefinePaymentTypeDataList l_dataList = new DefinePaymentTypeDataList();
		DefinePaymentType l_entity = new DefinePaymentType();
		ArrayList<DefinePaymentType> l_headerlist = new ArrayList<DefinePaymentType>();
		ArrayList<DefinePaymentTypeDetail> l_arr = new ArrayList<DefinePaymentTypeDetail>();
		
		/* String l_cri = " and dpt.InvoiceNo like N'%"+param.getKeyword() +"%'"
				+ " and InvoiceDate like N'%"+ param.getKeyword() +"%'"
				+ " and ReceivedBranchCode like N'%"+ param.getKeyword() + "%'"; */
		
		String l_cri = "", l_query = "";
		
		if(param.getFieldname().equals("InvoiceNo"))
			l_cri = "and dpt." + param.getFieldname() + " like N'%" + param.getKeyword() + "%'";
		else
			l_cri = "and " + param.getFieldname() + " like N'%" + param.getKeyword() + "%'";
		
		query = "set rowcount "
				+ param.getSize()
				+ " SELECT ### FROM (SELECT ROW_NUMBER()OVER (ORDER BY dpt.InvoiceID)No,dptd.*, dpt.BranchID, c.CurrencyCode "
				+ " FROM DefinePaymentTypeDetail dptd "
				+ " inner join DefinePaymentType dpt on dpt.InvoiceID = dptd.InvoiceID "
				+ " inner join Currency c on c.CurrencyID = dptd.CurrencyID "
				+ " WHERE 1=1 " + l_cri + " )a where a.BranchID = " + param.getBranchId(); 
		
		l_query = query;
		query += " and No > " + param.getIndex();
		query = query.replace("###", "*");
		
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		
		while(rs.next()){
			l_arr.add(readRecord(rs));
		}
		
		rs.close();
		pstmt.close();
		
		query = l_query.replace("###", "count (*)");
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		
		if(rs.next()){
			l_dataList.setTotalRecord(rs.getLong(1));
		}
		
		rs.close();
		pstmt.close();
		
		l_entity.setL_detail(l_arr);
		l_headerlist.add(l_entity);
		l_dataList.setEntityList(l_headerlist);
		
		
		return l_dataList;
	}

	@Override
	public Agent searchAgent(String mode, Agent agent) throws SQLException {
		Agent l_entity = null;
		query = "select * from Agent a inner join [Type] t on t.[type_id] = a.[type_id] where type_code = '" + mode + "' and a.agent_code = '" + agent.getAgent_code() + "' and a.branchID = " + agent.getBranchId();
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		if(rs.next()){
			l_entity = new Agent();
			l_entity.setAgent_id(rs.getLong("agent_id"));
			l_entity.setAgent_code(rs.getString("agent_code") == null ? "" : rs.getString("agent_code"));
		}
		return l_entity;
	}

	@Override
	public DefinePaymentTypeDetailDataList getReceiptNo(
			DefinePaymentTypeDetail entity) throws SQLException {
		DefinePaymentTypeDetailDataList l_DataList = new DefinePaymentTypeDetailDataList();
		List<DefinePaymentTypeDetail> l_entityList = new ArrayList<DefinePaymentTypeDetail>();
		
		query = " set rowcount " + entity.getSize() +
				" select ### from (select ROW_NUMBER() over (order by ReceiptNo)No,a.ReceiptNo from (" +
				" select ReceiptNo,DefaultBranchID from CashRemittanceByAgent_Archive " +
				"union all " +
				" select ReceiptNo,DefaultBranchID from CashRemittanceByAgent "+
				" union all "+
				" select ReceiptNo,BranchId DefaultBranchID from RemittanceCashReceived "+
				" union all " +
				" select ReceiptNo,BranchId DefaultBranchID from RemittanceCashReceivedArchive ) a " +
				" where a.DefaultBranchID = "+ entity.getBranchId() + ")b where No > "+entity.getIndex();
		
		String l_query = query;
		query = query.replace("###", "*");
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		while(rs.next()){
			DefinePaymentTypeDetail l_info = new DefinePaymentTypeDetail();
			l_info.setReceiptNo(rs.getString(field_receipt_no));
			l_entityList.add(l_info);
		}
		
		l_DataList.setEntityList(l_entityList);
		
		rs.close();
		pstmt.close();
		
		query = l_query.replace("###", "count (*)");
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		
		if(rs.next()){
			l_DataList.setTotalRecord(rs.getLong(1));
		}
		
		rs.close();
		pstmt.close();
		
		return l_DataList;
	}
}
